package com.yuanli.card.utils;


import com.yuanli.card.constant.ResponseCode;
import com.yuanli.card.exception.ServiceException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by layne on 2018/5/23.
 * 基于Poi的Excel工具类
 * 配合{@link ExcelTileFiledIndex} 快速创建表头 设置每列的索引值
 */
// TODO: 2018/5/23 有时间增加基于注解,添加注释,添加颜色等功能
public class ExcelUtils {
    /**
     * 解决长数字变成科学计数法或double的问题
     */
    private static DecimalFormat df = new DecimalFormat("0");

    private static SimpleDateFormat sdf = new SimpleDateFormat(DateUtils.YYYYMMDDHHMMSS);

    /**
     * 将Excel转成List
     *
     * @param excelStream
     * @param sheetName
     * @param columnNameFiledNameMap 列名和 tClass 中字段名的映射
     * @param tClass
     * @param <T>
     * @return
     */
    public static <T> List<T> excelToList(InputStream excelStream, String sheetName, Map<String, String> columnNameFiledNameMap, Class<T> tClass) {
        XSSFWorkbook workbook = null;
        try {
            workbook = new XSSFWorkbook(excelStream);
            return handleSheet(workbook.getSheet(sheetName), columnNameFiledNameMap, tClass);
        } catch (IOException e) {
            throw new ServiceException(ResponseCode.EXCEL_PARSE_ERROR, e);
        } finally {
            if (excelStream != null) {
                try {
                    excelStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 将Excel转成List
     *
     * @param excelStream
     * @param sheetIndex
     * @param columnNameFiledNameMap 列名和 tClass 中字段名的映射
     * @param tClass
     * @param <T>
     * @return
     */
    public static <T> List<T> excelToList(InputStream excelStream, Integer sheetIndex, ExcelTileFiledIndex excelTileFiledIndex, Class<T> tClass) {
        XSSFWorkbook workbook = null;
        try {
            workbook = new XSSFWorkbook(excelStream);
            return handleSheet(workbook.getSheetAt(sheetIndex), excelTileFiledIndex.getColumnNameFiledNameMap(), tClass);
        } catch (IOException e) {
            throw new ServiceException(ResponseCode.EXCEL_PARSE_ERROR, e);
        } finally {
            if (excelStream != null) {
                try {
                    excelStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * @param sourceData
     * @param excelPath
     * @param sheetName
     * @param columnNameIndexMap
     * @param columnNameFiledNameMap
     * @param <T>
     */
    public static <T> void listToExcel(List<T> sourceData,
                                       String excelPath,
                                       String sheetName,
                                       ExcelTileFiledIndex excelTileFiledIndex) {
        try {
            listToExcel(sourceData, new FileOutputStream(excelPath), sheetName, excelTileFiledIndex);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param sourceData
     * @param excelPath
     * @param sheetName
     * @param columnNameIndexMap
     * @param columnNameFiledNameMap
     * @param <T>
     */
    public static <T> void listToExcel(List<T> sourceData,
                                       OutputStream fos,
                                       String sheetName,
                                       ExcelTileFiledIndex excelTileFiledIndex) {
        XSSFWorkbook workbook = null;
        try {
            workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet(sheetName);
            //写表头
            writeSheetTitle(sheet, excelTileFiledIndex.getColumnNameIndexMap());
            //写表单数据
            writeSheetData(sheet, sourceData, excelTileFiledIndex);
            workbook.write(fos);
        } catch (IOException e) {
            throw new ServiceException(ResponseCode.EXCEL_PARSE_ERROR, e);
        } finally {
            try {
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * @param sheet
     * @param sourceData
     * @param columnNameIndexMap
     * @param columnNameFiledNameMap
     * @param tClass
     * @param <T>
     */
    private static <T> void writeSheetData(XSSFSheet sheet,
                                           List<T> sourceData,
                                           ExcelTileFiledIndex excelTileFiledIndex) {
        for (int i = 0; i < sourceData.size(); i++) {
            T t = sourceData.get(i);
            XSSFRow row = sheet.createRow(i + 1);
            excelTileFiledIndex.getColumnNameIndexMap().forEach((k, v) -> {
                //获取字段名称
                String filedName = excelTileFiledIndex.getColumnNameFiledNameMap().get(k);
                try {
                    //获取字段
                    Field field = t.getClass().getDeclaredField(filedName);
                    //设置访问权限
                    field.setAccessible(true);
                    XSSFCell cell = null;
                    Class<?> type = field.getType();
                    //创建单元格
                    Object filedValue = field.get(t);
                    if (filedValue != null) {
                        if (type.equals(Date.class)) {
                            cell = row.createCell(v, Cell.CELL_TYPE_STRING);
                            Date date = (Date) filedValue;
                            cell.setCellValue(DateUtils.simpleDateFormat(DateUtils.YYYYMMDDHHMMSS).format(date));
                        } else if (type.equals(Boolean.class)) {
                            cell = row.createCell(v, Cell.CELL_TYPE_BOOLEAN);
                            cell.setCellValue((Boolean) filedValue);
                        } else {
                            String stringValue = String.valueOf(filedValue);
                            //if (NumberUtils.isNumber(stringValue)) {
                            //数字全部转成字符串,避免科学计数法问题
                            if (false) {
                                cell = row.createCell(v, Cell.CELL_TYPE_NUMERIC);
                                cell.setCellValue(Double.valueOf(stringValue));
                            } else {
                                cell = row.createCell(v, Cell.CELL_TYPE_STRING);
                                cell.setCellValue(stringValue);
                            }
                        }
                    } else {
                        cell = row.createCell(v, Cell.CELL_TYPE_BLANK);
                    }
                } catch (NoSuchFieldException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }

            });
        }
    }

    /**
     * 写表头
     *
     * @param sheet
     * @param columnNameIndexMap
     */
    private static void writeSheetTitle(XSSFSheet sheet, Map<String, Integer> columnNameIndexMap) {
        XSSFRow row = sheet.createRow(0);
        columnNameIndexMap.forEach((k, v) -> {
            XSSFCell cell = row.createCell(v, Cell.CELL_TYPE_STRING);
            cell.setCellValue(k);
        });
    }

    /**
     * @param sheet
     * @param columnNameFiledNameMap
     * @param tClass
     * @param <T>
     * @return
     */
    private static <T> List<T> handleSheet(XSSFSheet sheet, Map<String, String> columnNameFiledNameMap, Class<T> tClass) {
        List<T> data = new ArrayList<>();
        Iterator<Row> rowIterator = sheet.rowIterator();
        Map<Integer, String> columnNumColumnNameMap = null;
        if (rowIterator.hasNext()) {
            //第一行是表头
            Row row0 = rowIterator.next();
            //处理表头
            columnNumColumnNameMap = handleFirstRow(row0);
        }
        //处理表单数据
        while (rowIterator.hasNext()) {
            data.add(parse(rowIterator.next(), columnNameFiledNameMap, columnNumColumnNameMap, tClass));
        }
        return data;
    }

    /**
     * 处理表头
     *
     * @param row 表头
     * @return 列索引对应的列名称
     */
    private static Map<Integer, String> handleFirstRow(Row row) {
        Map<Integer, String> cellNumCellNameMap = new HashMap<>();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            cellNumCellNameMap.put(cell.getColumnIndex(), cell.getStringCellValue());
        }
        return cellNumCellNameMap;
    }

    /**
     * 将每行的内容解析成对象
     *
     * @param row
     * @param cellNameFiledNameMap
     * @param cellNumCellNameMap
     * @param tClass
     * @param <T>
     * @return
     */
    private static <T> T parse(Row row, Map<String, String> columnNameFiledNameMap, Map<Integer, String> columnNumColumnNameMap, Class<T> tClass) {
        T instance = null;
        try {
            instance = tClass.newInstance();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                //列名
                String columnName = columnNumColumnNameMap.get(cell.getColumnIndex());
                System.out.println(columnName);
                System.out.println(cell.getColumnIndex());
                //地段名
                String filedName = columnNameFiledNameMap.get(columnName);
                System.out.println(filedName);
                //字段
                Field field = tClass.getDeclaredField(filedName);
                //填值
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            setValue(field, instance, cell.getDateCellValue());
                        } else {
                            setValue(field, instance, df.format(cell.getNumericCellValue()));
                        }
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        setValue(field, instance, cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        String val = null;
                        try {
                            val = String.valueOf(cell.getStringCellValue());
                        } catch (IllegalStateException e) {
                            val = String.valueOf(cell.getNumericCellValue());
                        }
                        setValue(field, instance, val);
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        setValue(field, instance, null);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        setValue(field, instance, cell.getStringCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        break;
                }

            }
        } catch (InstantiationException | IllegalAccessException | NoSuchFieldException e) {
            throw new ServiceException(ResponseCode.EXCEL_PARSE_ERROR, e);
        }
        return instance;
    }

    /**
     * 给字段赋值
     *
     * @param field
     * @param instance
     * @param cellvalue Excel单元格取出来的值
     * @throws IllegalAccessException
     */
    private static void setValue(Field field, Object instance, Object cellvalue) throws IllegalAccessException {
        //设置访问权限
        if (cellvalue != null) {
            field.setAccessible(true);
            Class<?> type = cellvalue.getClass();
            if (type.equals(Double.class)) {
                setNumericCellValue(field, instance, (Double) cellvalue);
            } else if (type.equals(Boolean.class)) {
                setBooleanCellValue(field, instance, (Boolean) cellvalue);
            } else if (type.equals(String.class)) {
                setStringCellValue(field, instance, (String) cellvalue);
            } else if (type.equals(Date.class)) {
                setDateCellValue(field, instance, (Date) cellvalue);
            } else {
                throw new ServiceException("UNSUPPORTTED TYPE");
            }
        }
    }

    /**
     * 从Excel中获取的是 Date类型 日期格式
     *
     * @param field
     * @param instance
     * @param cellvalue
     * @throws IllegalAccessException
     */
    private static void setDateCellValue(Field field, Object instance, Date cellvalue) throws IllegalAccessException {
        Class<?> type = field.getType();
        if (type.equals(Date.class)) {
            field.set(instance, cellvalue);
        } else if (type.equals(String.class)) {
            field.set(instance, sdf.format(cellvalue));
        } else {
            throw new ServiceException("UNSUPPORTTED TYPE");
        }
    }

    /**
     * 从excel中获取的是String 类型的值
     *
     * @param field
     * @param instance
     * @param cellvalue
     * @throws IllegalAccessException
     */
    private static void setStringCellValue(Field field, Object instance, String cellvalue) throws IllegalAccessException {
        Class<?> type = field.getType();
        if (type.equals(Integer.class)) {
            field.set(instance, Integer.parseInt(getIntStr(cellvalue)));
        } else if (type.equals(Double.class)) {
            field.set(instance, Double.parseDouble(cellvalue));
        } else if (type.equals(Long.class)) {
            field.set(instance, Long.parseLong(getIntStr(cellvalue)));
        } else if (type.equals(Short.class)) {
            field.set(instance, Short.parseShort(cellvalue));
        } else if (type.equals(Float.class)) {
            field.set(instance, Float.parseFloat(cellvalue));
        } else if (type.equals(String.class)) {
            field.set(instance, cellvalue);
        } else if (type.equals(Date.class)) {
            try {
                field.set(instance, sdf.parse(cellvalue));
            } catch (ParseException e) {
                throw new ServiceException("UNSUPPORTTED DATE FORMAT");
            }
        } else {
            throw new ServiceException("UNSUPPORTTED TYPE");
        }
    }

    /**
     * 从Excel中获取的是bool值
     *
     * @param field
     * @param instance
     * @param booleanCellValue
     * @throws IllegalAccessException
     */
    private static void setBooleanCellValue(Field field, Object instance, boolean booleanCellValue) throws IllegalAccessException {
        Class<?> type = field.getType();
        if (type.equals(Boolean.class)) {
            field.set(instance, booleanCellValue);
        } else if (type.equals(String.class)) {
            field.set(instance, String.valueOf(booleanCellValue));
        } else if (type.equals(Integer.class)) {
            field.set(instance, booleanCellValue ? 1 : 0);
        } else {
            throw new ServiceException("UNSUPPORTTED TYPE");
        }
    }

    /**
     * 从Excel中获取的是数值类型的值
     *
     * @param field
     * @param instance
     * @param numericCellValue
     * @throws IllegalAccessException
     */
    private static void setNumericCellValue(Field field, Object instance, Double numericCellValue) throws IllegalAccessException {
        Class<?> type = field.getType();
        if (type.equals(Integer.class)) {
            field.set(instance, numericCellValue.intValue());
        } else if (type.equals(Double.class)) {
            field.set(instance, numericCellValue);
        } else if (type.equals(Long.class)) {
            field.set(instance, numericCellValue.longValue());
        } else if (type.equals(Short.class)) {
            field.set(instance, numericCellValue.shortValue());
        } else if (type.equals(Float.class)) {
            field.set(instance, numericCellValue.floatValue());
        } else if (type.equals(String.class)) {
            field.set(instance, numericCellValue.toString());
        } else if (type.equals(Boolean.class)) {
            if (numericCellValue == 1) {
                field.set(instance, Boolean.TRUE);
            } else {
                field.set(instance, Boolean.FALSE);
            }
        } else {
            throw new ServiceException("UNSUPPORTTED TYPE");
        }
    }

    /**
     * 从excel中获取的是带小数点的值,转换需要去掉小数点
     *
     * @param source
     * @return
     */
    private static String getIntStr(String source) {
        if (source.contains(".")) {
            return source.substring(0, source.indexOf("."));
        }
        return source;
    }

    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

}
